"""
该脚本用来创建所需要要的数据库以及对应的数据表
测试数据库地址： 本机虚拟机 214
        端口： 5432
        账户：timeaxis
        密码：timeaxis

规定：数据库名称可以自定义，但是schema固定为timeaxis

"""
from .CONFIG import SCHEDULER
import psycopg2

"""

    -------------------------------------------------------------------------------
    数据库配置
    -------------------------------------------------------------------------------

"""
# USER = 'postgres'
# PASSWORD = 'timeaxis'
# HOST = '192.168.1.214'
# PORT= 5432
#DATABASE = schedule['database']
USER = SCHEDULER['user']
PASSWORD = SCHEDULER['password']
HOST = SCHEDULER['host']
PORT= SCHEDULER['port']




class DBManager(object):

    def __init__(self,db_name ='postgres',auto_create=True):
        super(DBManager, self).__init__()
        self.db_name = self.use_db(db_name,auto_create) #当前操作的数据库


    def _default_connect(self):
        """
        默认连接postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database='postgres',
                                          user=USER,
                                          password=PASSWORD,
                                          host=HOST,
                                          port=PORT)
            cursor = connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return connect, cursor


    def _init_214_db(self):
        """
        初始化231 cgtw数据库连接
        :param db_name: 默认连接自带的postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database=self.db_name,
                                          user=USER,
                                          password=PASSWORD,
                                          host=HOST,
                                          port=PORT)
            cursor = connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return connect, cursor

    """
    -------------------------------------------------------------------------------
    数据库操作
    -------------------------------------------------------------------------------
    """
    def use_db(self,db_name,auto_create):
        """
        切换指定数据库
        :param db_name:
        :param auto_create:
        :return:
        """
        db = ''
        select_sql ="select * from pg_database where datname='{}'".format(db_name)
        sql ="create database {} ".format(db_name)
        connect, cursor = self._default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchone()
            if not result and auto_create:
                connect.autocommit =True
                cursor.execute(sql)
                connect.autocommit =False
                db = db_name
                print('创建成功')
            else:
                print('数据库{}已经存在,执行该数据库操作'.format(db_name))
                db = result[0]
        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()
        print(db)
        return db

    def create_db(self,db_name):
        """
        创建新的数据库

        :return:
        """
        select_sql ="select * from pg_database where datname='{}'".format(db_name)
        sql ="create database {} ".format(db_name)
        connect, cursor = self._default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchall()
            if not result:
                connect.autocommit =True
                cursor.execute(sql)
                connect.autocommit =False
                print('创建成功')
            else:
                print('数据库{}已经存在'.format(db_name))

        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()


    def get_all_database(self):
        """
        获取当前所有的database
        :return:
        """
        sql= "select datname From pg_database"
        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            if result:
                for i in result:
                    print(i[0])
            else:
                print('没有database')
        except Exception as get_all_database_ERR:
            print(str(get_all_database_ERR))






    """
    -------------------------------------------------------------------------------
    schema操作
    -------------------------------------------------------------------------------
    """



    def select_schema(self):
        sql ="SELECT nspname FROM pg_namespace"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            result =cursor.fetchall()
            if result:
                for i in result:
                    print(i)
            else:
                print('没有schema')
        except Exception as select_schema_ERR:
            print(str(select_schema_ERR))


    def create_schema(self,schema_name):
        """
        创建schema
        :param db_name:
        :param schema_name:
        :return:
        """
        success = False
        sql ="create schema {}".format(schema_name)
        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('schema创建成功')
        except Exception as creat_schema_ERR:
            print('creat_schema_ERR:',str(creat_schema_ERR))
        finally:
            connect.close()
        return success


    """
    -------------------------------------------------------------------------------
    table操作
    -------------------------------------------------------------------------------
    """
    def create_account_table(self):
        """
        创建account表
        仅与排期软件自身有关，不从cgtw读取数据
        ***********************************************
            account表操作
            内容:记录排期软件用户信息，包括账户，密码，权限等级，UI配置，部门信息
            字段:
                id : serial PRIMARY KEY
                account : character varying(50) unique
                custom_json : jsonb 当前用户页面配置
                password : character varying(100)
                user_name :character varying(100)
                account_info :jsonb 用户自定义配置，暂时没用
                department :character varying(50)  人员所属部门，暂时没用
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.account(id serial PRIMARY KEY,account character varying(50) unique," \
              "user_level character varying(10)," \
              "custom_json jsonb," \
              "password character varying(100)," \
              "user_name character varying(100)," \
              "account_info jsonb," \
              "department character varying(50));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('account表创建成功')
        except Exception as create_account_table_ERR:
            print('create_account_table_ERR:',str(create_account_table_ERR))
        finally:
            connect.close()
        return success


    def create_r_account_table(self):
        """
        创建r_account表

        从cgtw的pulic下的config表中读取：部门id
        从cgtw的pulic下的entiy表中读取：人员account_id,人员等级，人员名称

        ***********************************************
            r_account表操作
            内容:记录cgtw用户信息，包括用户在cgtw中的id，用户名称，等级，cgtw中的部门id
            字段:
                id : serial PRIMARY KEY
                cg_id:character varying(40) unique  人员id
                department_id: character varying(100) cgtw部门id
                level :character varying(20) 人员等级
                artist: character varying(50)人员名称
                active_department: character varying(50) 初始所在部门

        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.r_account(id serial PRIMARY KEY," \
              "cg_id character varying(50) unique," \
              "department_id character varying(100)," \
              "level character varying(20)," \
              "artist character varying(200)," \
              "active_department character varying(50));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('r_account表创建成功')
        except Exception as create_r_account_table_ERR:
            print('create_r_account_table_ERR:',str(create_r_account_table_ERR))
        finally:
            connect.close()
        return success


    def create_public_conf_table(self):
        """
        创建public_conf表

        排期软件自身信息表，与cgtw无关。


        ***********************************************
            public_conf表操作
            内容:记录自定义 放假日期，token,所有阶段的pipeline
            字段:
                id : serial PRIMARY KEY
                public_info jsonb 公共数据存储，非工作日信息存储
                    自定义非工作日custom_non_work_day，具体到每一列
                    "custom_non_work_day": ["2019-10-05\n周六"]
                token : character varying(400) #查询图片的token,每日一更新
                pipeline :  jsonb  用于查询相关阶段
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.public_conf(id serial PRIMARY KEY, " \
              "public_info jsonb," \
              "token character varying(400)," \
              "pipeline jsonb);"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('public_conf表创建成功')
        except Exception as create_public_conf_table_ERR:
            print('create_public_conf_table_ERR:',str(create_public_conf_table_ERR))
        finally:
            connect.close()
        return success


    def create_projects_table(self):
        """
        创建projects表

        排期软件自身信息表，与cgtw无关。

        ***********************************************
            projects表操作
            内容:记录排期软件本周优先，项目窗口，半年报栏以及记录 备注点
            字段:
                id : serial PRIMARY KEY
                row_index :integer  表格行坐标
                column_index: integer  表格列坐标
                project_name:character varying(200)  项目名称
                            eg:古剑网大(GJWD)
                color: character varying(50) 项目颜色
                active : integer 是否显示
                inweek :  integer 是否显示在本周优先
                week_row_index :  integer  本周栏的行坐标
                inhalfyear : integer  是否显示在半年报栏
                halfyear_row_index : integer  半年报栏的行坐标
                halfyear_column_index : integer  半年报栏的列坐标
                database ：character varying(50) 项目schema名称
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.projects(id serial PRIMARY KEY, " \
              "row_index integer," \
              "column_index integer," \
              "project_name character varying(200) unique ," \
              "color character varying(50)," \
              "active integer," \
              "inweek integer," \
              "week_row_index integer," \
              "week_column_index integer," \
              "inhalfyear integer," \
              "halfyear_row_index integer," \
              "halfyear_column_index integer ," \
              "database character varying(50));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('projects表创建成功')
        except Exception as create_projects_table_ERR:
            print('create_projects_table_ERR:',str(create_projects_table_ERR))
        finally:
            connect.close()
        return success


    def create_nodes_table(self):
        """
        创建nodes表

        排期软件自身信息表，与cgtw无关。

        ***********************************************
            nodes表操作
            内容:记录排期软件 时间节点信息
            字段:
                id : serial PRIMARY KEY
                project_id: 关联projects中的id
                node_date: character varying(40) 节点日期
                node_info : text 节点备注说明
                weight : integer 紧急权重  0-100
                update_time : character varying(200)  更新日期
                updater : character varying(200)  更新/创建人员
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.nodes(id serial PRIMARY KEY, " \
              "project_id integer," \
              "node_date character varying(40)," \
              "node_info  text," \
              "weight integer," \
              "update_time character varying(200)," \
              "updater character varying(200));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('nodes表创建成功')
        except Exception as create_nodes_table_ERR:
            print('create_nodes_table_ERR:',str(create_nodes_table_ERR))
        finally:
            connect.close()
        return success


    def create_r_project_table(self):
        """
        创建r_project表

        从cgtw的public下的entity读取字段module为project的项目信息
        从cgtw的对应项目的schema下的entity读取所有eps信息

        ***********************************************
            r_project表操作
            内容:记录项目id，项目database(即项目的对应的schema名称),简称entity_name,中文名full_name,color,status,eps,
                pipeline_list,帧速率frame_rate
            字段:
                id : serial PRIMARY KEY
                cg_id : character varying(40) unique
                database : character varying(80)
                entity_name: character varying(50)拼音简称
                full_name:character varying(50) 中文名称
                color:character varying(10)
                status:character varying(20)项目状态
                resolution : character varying(20) 分辨率
                eps : text 每个项目的总集数 格式为 eps01|eps02|...

                # pipeline_list : jsonb  该项目的所有阶段
                #                 例：{"pipeline_list": ["mod_c","tex_c",...]}
                frame_rate :character varying(20) 帧速率
                path:character varying(200)  项目所在NAS路径
                project_material_path:character varying(200)  原shot所在NAS路径
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.r_project(id serial PRIMARY KEY," \
              "cg_id character varying(40) unique," \
              "database character varying(20)," \
              "entity_name character varying(50)," \
              "full_name character varying(50)," \
              "color character varying(10)," \
              "status character varying(20)," \
              "resolution character varying(20)," \
              "frame_rate character varying(20)," \
              "path character varying(200)," \
              "eps text," \
              "project_material_path character varying(200));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('r_project表创建成功')
        except Exception as create_r_project_table_ERR:
            print('create_r_project_table_ERR:',str(create_r_project_table_ERR))
        finally:
            connect.close()
        return success


    def create_r_department_table(self):
        """
        创建r_department表

        从cgtw的public下的config表中读取字段object为department的信息

        ***********************************************
            r_department表操作
            内容: 记录每个已分配任务的记录
            字段:
                id : serial PRIMARY KEY
                cg_id : character varying(100)  #这个就是config表中的部门id，所有部门信息指向这里
                name: character varying(50) 部门名称
                display : integer 是否显示当前部门到排期软件
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.r_department(id serial PRIMARY KEY," \
              "cg_id character varying(100) unique," \
              "name character varying(50)," \
              "display integer);"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('r_department表创建成功')
        except Exception as create_r_department_table_ERR:
            print('create_r_department_table_ERR:',str(create_r_department_table_ERR))
        finally:
            connect.close()
        return success


    def create_assigned_task_table(self):
        """
        创建assigned_task表

        与cgtw无关，记录每个已分配任务的记录

        ***********************************************
            assigned_task表操作
            内容: 记录每个已分配任务的记录
            字段:
                id : serial PRIMARY KEY
                task_type: character varying(20) 任务类型
                            cgtw或者custom
                cg_id : character varying(100)  #cgtw任务cg_id
                artist：character varying(200) 制作者
                show_name: character varying(200) 任务显示名称
                start_date：character varying(40) 任务起始日期
                end_date： character varying(40)  任务结束日期
                date_length : integer  #日期长度   任务跨天长度
                position： integer   任务位置
                assigner: character varying(200) 分配者
                assigned_time  ：character varying(200) 分配时间
                                2020-05-28 08:56:03
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.assigned_task(id serial PRIMARY KEY," \
              "task_type character varying(20)," \
              "cg_id character varying(100)," \
              "artist character varying(200)," \
              "show_name character varying(200)," \
              "start_date character varying(40)," \
              "end_date character varying(40)," \
              "date_length integer," \
              "position integer," \
              "assigner character varying(200)," \
              "assigned_time character varying(200));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('assigned_task表创建成功')
        except Exception as create_assigned_task_table_ERR:
            print('create_assigned_task_table_ERR:',str(create_assigned_task_table_ERR))
        finally:
            connect.close()
        return success


    def create_delete_task_table(self):
        """
        创建delete_task表

        与cgtw无关，记录每个已删除的任务的记录

        ***********************************************
            delete_task表操作
            内容: 记录每个已分配任务的记录
            字段:
                id : serial PRIMARY KEY
                task_type: character varying(20) 任务类型
                            cgtw或者custom
                cg_id : character varying(100)  #cgtw任务cg_id
                artist：character varying(200) 制作者
                show_name: text 任务显示名称,任务组可能会很长
                start_date：character varying(40) 任务起始日期
                end_date： character varying(40)  任务结束日期
                date_length : integer  #日期长度   任务跨天长度
                position： integer   任务位置
                deleted_by : character varying(200) 删除者
                delete_time : character varying(200) 删除时间
                                2020-05-28 08:56:03
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.delete_task(id serial PRIMARY KEY," \
              "task_type character varying(20)," \
              "cg_id character varying(100) ," \
              "artist character varying(200)," \
              "show_name text," \
              "start_date character varying(40)," \
              "end_date character varying(40)," \
              "date_length integer," \
              "position integer," \
              "deleted_by character varying(200)," \
              "delete_time character varying(200));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('delete_task表创建成功')
        except Exception as create_delete_task_table_ERR:
            print('create_delete_task_table_ERR:',str(create_delete_task_table_ERR))
        finally:
            connect.close()
        return success


    def create_custom_task_table(self):
        """
        创建custom_task表

        与cgtw无关，记录排期自身的自定义任务信息

        ***********************************************
            custom_task表操作
            内容: 记录排期自身的自定义任务信息
            字段:
                id : serial PRIMARY KEY
                cg_id : character varying(100)  #cgtw任务cg_id 注意这里不是unique，可以重复
                start_date：character varying(40) 任务起始日期
                end_date： character varying(40)  任务结束日期
                artist:character varying(200) 制作者
                workhour:character varying(40)工时
                description :text 任务描述
                task_type: character varying(15)  任务类型
                custom_type: character varying(15)用户自定义任务类型
                note :text 备注
                position： integer   任务位置
                show_workhour: character varying(40)显示工时
                parent_data : jsonb如果是非子任务，那么就是空,如果是子任务,那存入需要的父任务的部分data数据
                show_name :character varying(200)#显示名称
                assigned_time:character varying(40) 分配时间
                department：character varying(50) 人员所在部门
                project_name:character varying(200)#项目名称
                color:character varying(50) 任务条颜色
                date_length : integer  #日期长度   任务跨天长度
                ignore_weekend :integer 是否忽略周末
                font_color:character varying(50) 字体颜色 用来记录标记属性
                database : character varying(30)  项目db
                assigner : character varying(100) 分配者
                status : character varying(50)   手动设置的任务状态
                account_id : character varying(100)   人员id，避免重名
        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.custom_task(id serial PRIMARY KEY," \
              "cg_id character varying(100) ," \
              "start_date character varying(40)," \
              "end_date character varying(40)," \
              "artist character varying(200)," \
              "workhour character varying(40)," \
              "description text," \
              "task_type  character varying(15)," \
              "custom_type character varying(40) ," \
              "note text," \
              "position integer," \
              "show_workhour character varying(40)," \
              "parent_data jsonb," \
              "show_name character varying(200)," \
              "assigned_time character varying(40)," \
              "department character varying(50)," \
              "project_name character varying(200)," \
              "color character varying(50)," \
              "date_length integer," \
              "ignore_weekend integer," \
              "font_color character varying(50)," \
              "database  character varying(30)," \
              "assigner  character varying(100)," \
              "status  character varying(50)," \
              "account_id  character varying(100));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('custom_task表创建成功')
        except Exception as create_custom_task_table_ERR:
            print('create_custom_task_table_ERR:',str(create_custom_task_table_ERR))
        finally:
            connect.close()
        return success


    def create_task_table(self):
        """
        创建task表

        与cgtw无关，记录排期自身的任务信息

        ***********************************************
            task表操作
            内容: 记录排期自身的任务信息
            字段:
                id : serial PRIMARY KEY
                task_type: character varying(15)  任务类型
                r_id : integer  r_task表中对应任务
                artist:character varying(200) 制作者
                position： integer   任务位置
                start_date：character varying(40) 任务起始日期
                end_date： character varying(40)  任务结束日期
                status : character varying(50)   手动设置的任务状态
                cg_id : character varying(100) unique  #cgtw任务cg_id
                retake_info : jsonb  返修记录
                            {0：data,1:data,2:data}
                            所需要的data为：
                                        artist,position,start_date,end_date,status，这个为分配的时的状态,
                                        artist_level,difficulty_level,workhour,description,assigned_time
                task_name : character varying(300) cgtw任务名称
                task_pipeline:  character varying(20)任务阶段 r_task里的entity_name
                project_name:character varying(200)#项目名称
                artist_level:  character varying(5)人员等级
                difficulty_level:  character varying(5)镜头难度等级
                workhour:character varying(40)工时
                module: character varying(20) 模块  asset/shot
                description :text 任务描述
                assigned_time:character varying(40) 分配时间
                eps : character varying(40) 集数
                img_path:character varying(200) 缩略图
                important_task_mark:character varying(40)重点任务标记
                show_name :character varying(200)#显示名称
                assigner : character varying(100) 分配者
                database : character varying(100) 项目schema名称
                note :text 备注
                project_shortname: 项目简写名称eg: WXFS
                show_workhour: character varying(40)显示工时
                client_status_retake_count : integer 返修次数
                sort_right : integer 排序正确
                assign_pipeline :character varying(400) 已经分配的阶段
                department：character varying(50) 人员所在部门
                project_color: character varying(50)项目颜色
                retake_workhour : character varying(200) 返修时间记录，每次同步时更新
                                eg: 2.5,13,5.3,12.5  以逗号隔开
                date_length : integer  #日期长度   任务跨天长度
                ignore_weekend :integer 是否忽略周末
                last_submit_time: character varying(50) 最后提交时间
                separated : integer  是否为拆分任务
                frame: character varying(50) 帧数
                ingroup: integer  是否在组里
                        0：不在组
                        1：在组里
                        2：是组的元素，但是被遗忘在外面
                cgworkhour:character varying(40)   cgtw上该任务的任务工时，不考虑timelog
                assigned_times: integer   安排次数(默认是1)
                assigned_workhour :character varying(40)  安排工时
                name:character varying(100) 镜头名称 不是显示名称
                deadline :character varying(30)  交片节点
                account_id : character varying(100)   人员id，避免重名

        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.task(id serial PRIMARY KEY," \
              "task_type character varying(15)," \
              "r_id integer," \
              "artist character varying(200)," \
              "position integer," \
              "start_date character varying(40)," \
              "end_date character varying(40)," \
              "status character varying(10)," \
              "cg_id character varying(100) unique," \
              "retake_info jsonb," \
              "task_name character varying(200)," \
              "task_pipeline character varying(200)," \
              "project_name character varying(40)," \
              "artist_level character varying(5)," \
              "difficulty_level character varying(5)," \
              "workhour character varying(40)," \
              "module character varying(20)," \
              "description text," \
              "assigned_time character varying(40)," \
              "eps character varying(40)," \
              "img_path character varying(200)," \
              "important_task_mark character varying(40)," \
              "show_name character varying(200)," \
              "assigner character varying(100)," \
              "database character varying(100)," \
              "note text," \
              "project_shortname character varying(100)," \
              "show_workhour character varying(40)," \
              "client_status_retake_count integer," \
              "sort_right integer," \
              "assign_pipeline character varying(400)," \
              "department character varying(50)," \
              "project_color character varying(50)," \
              "retake_workhour character varying(200)," \
              "date_length integer," \
              "ignore_weekend integer," \
              "last_submit_time character varying(50)," \
              "separated integer," \
              "frame character varying(50)," \
              "ingroup integer," \
              "cgworkhour character varying(40)," \
              "assigned_times integer, " \
              "assigned_workhour character varying(40)," \
              "name character varying(100)," \
              "deadline character varying(30)," \
              "account_id character varying(100));"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('task表创建成功')
        except Exception as create_task_table_ERR:
            print('create_task_table_ERR:',str(create_task_table_ERR))
        finally:
            connect.close()
        return success




    def create_group_task_table(self):
        """
        创建group_task表

        与cgtw无关，记录排期自身的任务组信息

        ***********************************************
            group_task表操作
            内容: 记录排期自身的自定义任务信息
            字段:
                id : serial PRIMARY KEY
                group_name : character varying(200)  任务组名称
                color:character varying(50) 任务颜色
                project_name:character varying(200)#项目名称
                department：character varying(50) 人员所在部门
                start_date：character varying(40) 任务起始日期
                end_date: character varying(40)  任务结束日期
                date_length : integer  #日期长度   任务组跨天长度，现在都是1
                workhour:character varying(40)工时
                artist:character varying(200) 制作者
                position： integer   任务组位置
                artist_level : character varying(40) 人员等级
                note :text 备注
                assigned_time:character varying(40) 分配时间
                task_id_dict : jsonb 子任务id
                tasks : text 子任务名称 拼接字符串
                use_custom_workhour : integer 是否使用自定义工时
                database : character varying(30)  项目db
                account_id : character varying(100)   人员id，避免重名

        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.group_task(id serial PRIMARY KEY," \
              "group_name character varying(200) ," \
              "color character varying(50)," \
              "project_name character varying(200)," \
              "department character varying(50)," \
              "start_date character varying(40)," \
              "end_date character varying(40)," \
              "date_length integer," \
              "workhour character varying(40)," \
              "artist character varying(200)," \
              "position integer," \
              "artist_level character varying(40)," \
              "note text,"\
              "assigned_time character varying(40)," \
              "task_id_dict jsonb," \
              "tasks text," \
              "use_custom_workhour integer," \
              "database character varying(30)," \
              "account_id character varying(100))"

        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
        except Exception as create_group_task_table_ERR:
            print('create_group_task_table_ERR:',str(create_group_task_table_ERR))
        finally:
            connect.close()
        return success




    def create_r_task_table(self):
        """
        创建r_task表

        从该schema名称获取 database
        通过timelog计算获取 last_workhour 最后的工时与 retake_workhour
        通过artist名称获取 department
        从各项目中的entity表里获取：
            1.module为shot 且 module_type为task的 镜头任务信息
                artist
                module : asset/shot
                task_name : 即json_data中的entity键
                status
                client_status_retake_count
                assigned : 是否已分配，仅排期软件任务标识之一
                difficulty_level
                important_task_mark
                workhour
                last_submit_time
                task_priority

                通过该任务的字段link_id 获取其对应的 shot info信息
                    shot 信息：
                        description : info json_data中的description，如果有vfx_note，那么就用vfx_note
                        name : info json_data中的entity键
                        frame : info json_data中的frame键
                        img_path : info json_data中的image键在的值，需转字典再取['min']
                        assign_pipeline  : info json_data中的assign_pipeline，已逗号隔开的
                        shot_difficulty_level : info json_data中的shot_difficulty_level
                        deadline : info json_data中的deadline
                        eps : 需要通过shot info该行的link_id再获取module为eps的信息(不需查询字段),在json_data中获取
                              entity键


            2.module为shot 且 module_type为asset的 资产任务信息
                通过该任务的字段link_id 获取其对应的 asset信息
                    asset 信息：
                        description : info json_data中的description，如果有vfx_note，那么就用vfx_note
                        name : info json_data中的中文名称 cn_name
                        assign_pipeline  : info json_data中的assign_pipeline，已逗号隔开的
                        frame : info json_data中的frame键
                        eps : 需要通过asset info该行的link_id再获取module为asset_type的信息(不需查询字段),在json_data中获取
                              entity键




        ***********************************************
            r_task表操作
            内容: 记录排期自身的自定义任务信息
            字段:
                id : serial PRIMARY KEY
                cg_id : character varying(100) unique #cgtw任务cg_id
                description : text #资产或镜头描述
                name : character varying(200)资产或者镜头名称
                eps : character varying(40) 集数
                img_path : character varying(200) 缩略图  资产貌似没有
                frame : character varying(50) 总帧数
                assign_pipeline : character varying(400) 相关阶段
                shot_difficulty_level : character varying(50) 镜头难度 ,资产貌似没有
                deadline : character varying(50) 交片时间 资产貌似没有
                artist:character varying(200) 制作者
                module : character varying(40)  asset/shot
                task_name : character varying(100) 任务名称
                status : character varying(50)   任务状态
                client_status_retake_count : integer 客户返修次数,需要加上导演次数
                assigned : integer 是否已分配，仅排期软件任务标识之一
                database : character varying(50)
                difficulty_level : character varying(40)
                important_task_mark : character varying(50)
                workhour : character varying(40)
                last_workhour : character varying(40)
                department : character varying(50)
                retake_workhour : character varying(40)
                last_submit_time : character varying(50)
                task_priority : character varying(40)
                task_pipeline : character varying(50) 任务所在阶段
                pinyin: character varying(200) 如果是资产，则记录拼音名称，如果是镜头等同与镜头名称
                account_id : character varying(100) 任务所在阶段

        ***********************************************

        :return:
        """
        success =False
        sql = "create table timeaxis.r_task(id serial PRIMARY KEY," \
              "cg_id character varying(100) unique ," \
              "description text," \
              "name character varying(200)," \
              "eps character varying(40)," \
              "img_path character varying(200)," \
              "frame character varying(50)," \
              "assign_pipeline character varying(400)," \
              "shot_difficulty_level character varying(50)," \
              "deadline character varying(50)," \
              "artist character varying(200)," \
              "module character varying(40)," \
              "task_name character varying(100)," \
              "status character varying(50)," \
              "client_status_retake_count integer," \
              "assigned integer," \
              "database character varying(50)," \
              "difficulty_level character varying(40)," \
              "important_task_mark character varying(50)," \
              "workhour character varying(40)," \
              "last_workhour character varying(40)," \
              "department character varying(50)," \
              "retake_workhour character varying(40)," \
              "last_submit_time character varying(50)," \
              "task_priority character varying(40)," \
              "task_pipeline character varying(50)," \
              "pinyin character varying(200)," \
              "account_id character varying(100));"


        connect, cursor = self._init_214_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('r_task表创建成功')
        except Exception as create_r_task_table_ERR:
            print('create_r_task_table_ERR:',str(create_r_task_table_ERR))
        finally:
            connect.close()
        return success



    """
    -------------------------------------------------------------------------------
    一键创建数据库以及所需要的表
    -------------------------------------------------------------------------------
    """
    def _init_scheduler_tables(self,schema_name ='timeaxis'):


        self.create_schema(schema_name)
        self.create_account_table()
        self.create_r_account_table()
        self.create_public_conf_table()
        self.create_projects_table()
        self.create_nodes_table()
        self.create_r_project_table()
        self.create_r_department_table()
        self.create_assigned_task_table()
        self.create_delete_task_table()
        self.create_custom_task_table()
        self.create_task_table()
        self.create_r_task_table()
        self.create_group_task_table()









